
package miniproject;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.*;
import javax.swing.JOptionPane;
/**
 *
 * @author Burim Sadriu
 */
public class dataBase {
 
    public int nbrOfProducts() {
        int numberOfProd = 0;
        try {
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                numberOfProd++;
            }
            System.out.println();
            System.out.println(" Number of products :" + numberOfProd); // Prints the number of products.
            c.close();
        } catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
        return numberOfProd;
    }

    public int nbrOfCustomers() {
        int numberOfCust = 0;
        try {
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customers");
            while (rs.next()) {
                numberOfCust++;
            }
            System.out.println();
            System.out.println(" Number of customers :" + numberOfCust); // Prints the number of customers.
            System.out.println();
            c.close();
        } catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
        return numberOfCust;
    }
    public void printProducts() {
        try {
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                int price = rs.getInt("price");
                String name = rs.getString("name");
                System.out.format("Product: %s, Price: %s \n", name, price );
            }
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
    public void printCustomers() {
        try {
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customers");
            
            while (rs.next()) {
                String userName = rs.getString("userName");
                String password = rs.getString("password");
                // print the results
                System.out.format("Name: %s, Password: %s \n", userName, password);
            }
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
    public void insertProducts(String name, int price) {
        try {
            String productName = name;
            //int productPrice = Integer.parseInt(price);
            int productPrice = price;
            System.out.println(productPrice);
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            String insertProd = "INSERT INTO products VALUES ('"+productName+"','"+productPrice+"')";
            st.executeUpdate(insertProd);
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
    public void insertCustomers(String userN, String pass) {
        try {
            String userName = userN;
            String password = pass;
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            String insertCust = "INSERT INTO customers VALUES ('"+userName+"','"+password+"')";
            st.executeUpdate(insertCust);
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
    public void deleteCustomers(String pass) {
        try {
            String password = pass;
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            String deleteCust = "DELETE FROM customers where password = '"+password+"'";
            st.executeUpdate(deleteCust);
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }
    public void deleteProducts(String name) {
        try {
            String productName = name;
            Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
            Statement st = (Statement) c.createStatement();
            String deleteProd = "DELETE FROM products where name = '"+productName+"'";
            st.executeUpdate(deleteProd);
            st.close();
            c.close();
        }
        catch (Exception e) {
            System.err.println("ERROR: " + e);
        }
    }

     //We check if a costumer already exists in database, if not we add him automatically!
     public void validateCustomer(String userName, String password) {
         try {
             Connection c1 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","root");
             Statement st1 = (Statement) c1.createStatement();
             ResultSet rs1 = st1.executeQuery("SELECT userName, password FROM customers WHERE userName LIKE '"+'%'+userName+'%'+"'");
             //ResultSet rs2 = st1.executeQuery("SELECT password FROM costumers WHERE password LIKE '"+'%'+password+'%'+"'");
             if(rs1.next()){
                 System.out.println("You are a member" );
                 //JOptionPane.showMessageDialog(null,"Already Exists");
             }
             else {
                 String insertCostumer = "Insert into customers values('"+userName+"','"+password+"')";
                 st1.executeUpdate(insertCostumer);
                 JOptionPane.showMessageDialog(null,"Data is successfully inserted into the database." );
             }
             st1.close();
             c1.close();
         }
         catch(Exception e) {
             System.out.println(e);
         }
     }
}
